
--exec sp_vertical_report 'ALL',2005,'0'

 ALTER     PROC sp_vertical_report
 ( @QUARTER VARCHAR(3),
   @YEAR INT,
   @defaultPromt VARCHAR(50) )
 
 AS

 BEGIN
 	DECLARE @MONTH_FROM INT
 	DECLARE @MONTH_TO INT
 	
 	DECLARE @START_MONTH INT
 	DECLARE @END_MONTH INT
 
 	DECLARE @TOTAL_BACKLOG_OPEN_REQ FLOAT
 	DECLARE @TOTAL_BACKLOG_HOLD_REQ FLOAT
	SET @TOTAL_BACKLOG_HOLD_REQ = 0
 	DECLARE @TOTAL_BACKLOG_OPEN_SCH FLOAT
 	DECLARE @TOTAL_BACKLOG_HOLD_SCH FLOAT
	SET @TOTAL_BACKLOG_HOLD_SCH = 0

	DECLARE @TOTAL_BACKLOG_HOLD_REQ_SALES FLOAT
	SET @TOTAL_BACKLOG_HOLD_REQ_SALES = 0
	DECLARE @TOTAL_BACKLOG_HOLD_REQ_MARKETING FLOAT
	SET @TOTAL_BACKLOG_HOLD_REQ_MARKETING = 0
	DECLARE @TOTAL_BACKLOG_HOLD_REQ_FINANCE FLOAT
	SET @TOTAL_BACKLOG_HOLD_REQ_FINANCE = 0
	DECLARE @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS FLOAT
	SET @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS = 0
	DECLARE @BACKLOG_HOLD_REQ_SALES FLOAT
	SET @BACKLOG_HOLD_REQ_SALES = 0
	DECLARE @BACKLOG_HOLD_REQ_MARKETING FLOAT
	SET @BACKLOG_HOLD_REQ_MARKETING = 0
	DECLARE @BACKLOG_HOLD_REQ_FINANCE FLOAT
	SET @BACKLOG_HOLD_REQ_FINANCE =0
	DECLARE @BACKLOG_HOLD_REQ_OPERATIONS FLOAT
	SET @BACKLOG_HOLD_REQ_OPERATIONS = 0
	DECLARE @BACKLOG_HOLD_REQ FLOAT
	SET @BACKLOG_HOLD_REQ = 0

	DECLARE @TOTAL_BACKLOG_HOLD_SCH_SALES FLOAT
	SET @TOTAL_BACKLOG_HOLD_SCH_SALES = 0
	DECLARE @TOTAL_BACKLOG_HOLD_SCH_MARKETING FLOAT
	SET @TOTAL_BACKLOG_HOLD_SCH_MARKETING = 0
	DECLARE @TOTAL_BACKLOG_HOLD_SCH_FINANCE FLOAT
	SET @TOTAL_BACKLOG_HOLD_SCH_FINANCE = 0
	DECLARE @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS FLOAT
	SET @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS = 0
	DECLARE @BACKLOG_HOLD_SCH_SALES FLOAT
	SET @BACKLOG_HOLD_SCH_SALES = 0
	DECLARE @BACKLOG_HOLD_SCH_MARKETING FLOAT
	SET @BACKLOG_HOLD_SCH_MARKETING = 0
	DECLARE @BACKLOG_HOLD_SCH_FINANCE FLOAT
	SET @BACKLOG_HOLD_SCH_FINANCE =0
	DECLARE @BACKLOG_HOLD_SCH_OPERATIONS FLOAT
	SET @BACKLOG_HOLD_SCH_OPERATIONS = 0
	DECLARE @BACKLOG_HOLD_SCH FLOAT
	SET @BACKLOG_HOLD_SCH = 0
 
 	DECLARE @TOTAL_BACKLOG_REQ FLOAT
 	DECLARE @TOTAL_BACKLOG_SCH FLOAT
 
 	DECLARE @TOTAL_SHIPPED_REQ FLOAT 
 	DECLARE @TOTAL_SHIPPED_SCH FLOAT 
 
 	DECLARE @TOTAL_RMA_REQ FLOAT
 	DECLARE @TOTAL_RMA_SCH FLOAT
 	DECLARE @TOTAL_RMA_HOLD_REQ FLOAT
 	DECLARE @TOTAL_RMA_HOLD_SCH FLOAT
 
 	DECLARE @TOTAL_NET_QTD FLOAT
 	DECLARE @TOTAL_CANCELLED_QTD FLOAT
 	DECLARE @TOTAL_GROSS_QTD FLOAT
 	
 	DECLARE @TOTAL_TURNS_BOOKING FLOAT
 	DECLARE	@BOOK_TO_BILL_QTD FLOAT

	DECLARE @TOTAL_PULL_IN_QTY FLOAT
	SET @TOTAL_PULL_IN_QTY = 0
	DECLARE @PULL_IN_QTY FLOAT
	SET @PULL_IN_QTY = 0
	DECLARE @QUARTER_END_DATE DATETIME
	DECLARE @QCOUNT INT
	SET @QCOUNT = 0

 	DECLARE @TOTAL FLOAT
 
 	SET @MONTH_TO = 3
 	IF(@QUARTER = 'All')
 	BEGIN
 		SET @MONTH_FROM = 0
 		SET @MONTH_TO = 12
 		SET @START_MONTH = 0
 		SET @END_MONTH = 11
 	END
 	ELSE IF(@QUARTER = '1')
 	BEGIN
 		SET @MONTH_FROM = 0
 		SET @START_MONTH = 0
 		SET @END_MONTH = 2
		SET @QUARTER_END_DATE = '03/31/'+CONVERT(VARCHAR,@YEAR)
 	END
 	ELSE IF(@QUARTER = '2')
 	BEGIN
 		SET @MONTH_FROM = 3
 		SET @START_MONTH = 0
 		SET @END_MONTH = 5
		SET @QUARTER_END_DATE = '06/30/'+CONVERT(VARCHAR,@YEAR)
 	END
 	ELSE IF(@QUARTER = '3')
 	BEGIN
 		SET @MONTH_FROM = 6
 		SET @START_MONTH = 0
 		SET @END_MONTH = 8
		SET @QUARTER_END_DATE = '09/30/'+CONVERT(VARCHAR,@YEAR)
 	END
 	ELSE IF(@QUARTER = '4')
 	BEGIN
 		SET @MONTH_FROM = 9
 		SET @START_MONTH = 0
 		SET @END_MONTH = 11
		SET @QUARTER_END_DATE = '12/31/'+CONVERT(VARCHAR,@YEAR)
 	END

 	DECLARE @RESULTS Table(TOTAL_SHIPPED_REQ FLOAT, TOTAL_SHIPPED_SCH FLOAT, TOTAL_BACKLOG_REQ FLOAT, TOTAL_BACKLOG_SCH FLOAT, TOTAL_RMA_REQ FLOAT, TOTAL_RMA_SCH FLOAT, TOTAL_BACKLOG_HOLD_REQ FLOAT, TOTAL_BACKLOG_HOLD_SCH FLOAT, TOTAL_RMA_HOLD_REQ FLOAT, TOTAL_RMA_HOLD_SCH FLOAT, TOTAL_GROSS_QTD FLOAT, TOTAL_CANCELLED_QTD FLOAT, TOTAL_NET_QTD FLOAT, TOTAL_TURNS_BOOKING FLOAT, BOOK_TO_BILL_QTD FLOAT, TOTAL_BACKLOG_HOLD_REQ_SALES FLOAT, TOTAL_BACKLOG_HOLD_REQ_MARKETING FLOAT, TOTAL_BACKLOG_HOLD_REQ_FINANCE FLOAT, TOTAL_BACKLOG_HOLD_REQ_OPERATIONS FLOAT, TOTAL_BACKLOG_HOLD_SCH_SALES FLOAT, TOTAL_BACKLOG_HOLD_SCH_MARKETING FLOAT, TOTAL_BACKLOG_HOLD_SCH_FINANCE FLOAT, TOTAL_BACKLOG_HOLD_SCH_OPERATIONS FLOAT, TOTAL_PULL_IN_QTY FLOAT )
 
 	DECLARE @TO_MONTH INT	
 	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO
 	
	DECLARE @CURRENT_DATE DATETIME
	SET @CURRENT_DATE = CONVERT(datetime,CURRENT_TIMESTAMP)

	DECLARE @CURRENT_QUARTER INT
	SET @CURRENT_QUARTER = DATEPART(q, GETDATE())

	/*IF( @QUARTER >= CAST(@CURRENT_QUARTER AS VARCHAR(3)) AND  @QUARTER <> 'All' )
	BEGIN*/
 	SELECT @TOTAL_BACKLOG_OPEN_REQ = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'APPROVED', '0', 'REQUESTED', @CURRENT_DATE) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 	--SELECT @TOTAL_BACKLOG_HOLD_REQ = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 	
 	SELECT @TOTAL_BACKLOG_OPEN_SCH = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'APPROVED', '0', 'SCHEDULE', @CURRENT_DATE) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 	--SELECT @TOTAL_BACKLOG_HOLD_SCH = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 	--END

	declare @CoolCursor CURSOR
	declare @LineItem_ID int
	DECLARE @CNT INT 
	DECLARE @HOLD_TEXT VARCHAR(30)

        set @CoolCursor = CURSOR for
	select LineItem_ID from Customer_Open_Order_LineItem COOL,Sales_Order
	where 
	COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
	and Sales_Order.SO_Is_Sample = 1
	and COOL_Status = 'HOLD'
	
	open @CoolCursor 
	       fetch next from @CoolCursor into @LineItem_ID
	while @@fetch_status = 0
	begin
		SELECT  @CNT = COUNT(*) FROM SALES_HOLD_TABLE WHERE LINEITEM_ID  = @LineItem_ID AND HOLD_BY_GROUP IN ('OPERATIONS','MARKETING','SALES','FINANCE') AND IS_APPROVED = 1
		SET @HOLD_TEXT = CASE 
			WHEN @CNT = 1 THEN  ( SELECT HOLD_BY_GROUP FROM SALES_HOLD_TABLE WHERE LINEITEM_ID  = @LineItem_ID AND HOLD_BY_GROUP IN ('OPERATIONS','MARKETING','SALES','FINANCE') AND IS_APPROVED = 1 )
			WHEN @CNT > 1 THEN 'ALL'
		END
		
		IF( @HOLD_TEXT = 'SALES' )
		BEGIN
			SELECT @BACKLOG_HOLD_REQ_SALES = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE, 'SALES', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_REQ_SALES IS NULL ) SET @BACKLOG_HOLD_REQ_SALES = 0
			SET @TOTAL_BACKLOG_HOLD_REQ_SALES = ( @BACKLOG_HOLD_REQ_SALES + @TOTAL_BACKLOG_HOLD_REQ_SALES )
			SELECT @BACKLOG_HOLD_SCH_SALES = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE, 'SALES', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_SCH_SALES IS NULL ) SET @BACKLOG_HOLD_SCH_SALES = 0
			SET @TOTAL_BACKLOG_HOLD_SCH_SALES = ( @BACKLOG_HOLD_SCH_SALES + @TOTAL_BACKLOG_HOLD_SCH_SALES )
		END
		ELSE IF( @HOLD_TEXT = 'MARKETING' )
		BEGIN	
			SELECT @BACKLOG_HOLD_REQ_MARKETING = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE, 'MARKETING', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
			IF( @BACKLOG_HOLD_REQ_MARKETING IS NULL ) SET @BACKLOG_HOLD_REQ_MARKETING = 0
			SET @TOTAL_BACKLOG_HOLD_REQ_MARKETING = ( @BACKLOG_HOLD_REQ_MARKETING + @TOTAL_BACKLOG_HOLD_REQ_MARKETING )
			SELECT @BACKLOG_HOLD_SCH_MARKETING = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE, 'MARKETING', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
			IF( @BACKLOG_HOLD_SCH_MARKETING IS NULL ) SET @BACKLOG_HOLD_SCH_MARKETING = 0	
			SET @TOTAL_BACKLOG_HOLD_SCH_MARKETING = ( @BACKLOG_HOLD_SCH_MARKETING + @TOTAL_BACKLOG_HOLD_SCH_MARKETING )
		END	
		ELSE IF( @HOLD_TEXT = 'FINANCE' )
		BEGIN		
			SELECT @BACKLOG_HOLD_REQ_FINANCE = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE, 'FINANCE', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_REQ_FINANCE IS NULL ) SET @BACKLOG_HOLD_REQ_FINANCE = 0		
			SET @TOTAL_BACKLOG_HOLD_REQ_FINANCE = ( @BACKLOG_HOLD_REQ_FINANCE + @TOTAL_BACKLOG_HOLD_REQ_FINANCE )
			SELECT @BACKLOG_HOLD_SCH_FINANCE = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE, 'FINANCE', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_SCH_FINANCE IS NULL ) SET @BACKLOG_HOLD_SCH_FINANCE = 0	
			SET @TOTAL_BACKLOG_HOLD_SCH_FINANCE = ( @BACKLOG_HOLD_SCH_FINANCE + @TOTAL_BACKLOG_HOLD_SCH_FINANCE )
		END
		ELSE IF( @HOLD_TEXT = 'OPERATIONS' )
		BEGIN
			SELECT @BACKLOG_HOLD_REQ_OPERATIONS = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE, 'OPERATIONS', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_REQ_OPERATIONS IS NULL ) SET @BACKLOG_HOLD_REQ_OPERATIONS = 0
			SET @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS = ( @BACKLOG_HOLD_REQ_OPERATIONS + @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS )
			SELECT @BACKLOG_HOLD_SCH_OPERATIONS = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE, 'OPERATIONS', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)			
			IF( @BACKLOG_HOLD_SCH_OPERATIONS IS NULL ) SET @BACKLOG_HOLD_SCH_OPERATIONS = 0
			SET @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS = ( @BACKLOG_HOLD_SCH_OPERATIONS + @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS )
		END
		ELSE IF ( @HOLD_TEXT = 'ALL' )
		BEGIN
			SELECT @BACKLOG_HOLD_REQ = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED', @CURRENT_DATE, '', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
			IF( @BACKLOG_HOLD_REQ IS NULL ) SET @BACKLOG_HOLD_REQ = 0
			SET @TOTAL_BACKLOG_HOLD_REQ = ( @BACKLOG_HOLD_REQ + @TOTAL_BACKLOG_HOLD_REQ )
			SELECT @BACKLOG_HOLD_SCH = Amount FROM fn_backlog_hold_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE', @CURRENT_DATE, '', @LineItem_ID) WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
			IF( @BACKLOG_HOLD_SCH IS NULL ) SET @BACKLOG_HOLD_SCH = 0
			SET @TOTAL_BACKLOG_HOLD_SCH = ( @BACKLOG_HOLD_SCH + @TOTAL_BACKLOG_HOLD_SCH )
		END

		set @LineItem_ID = NULL
		set @HOLD_TEXT = NULL
		SET @BACKLOG_HOLD_REQ_SALES = 0
		SET @BACKLOG_HOLD_REQ_MARKETING = 0
		SET @BACKLOG_HOLD_REQ_FINANCE = 0
		SET @BACKLOG_HOLD_REQ_OPERATIONS = 0
		SET @BACKLOG_HOLD_REQ = 0
		SET @BACKLOG_HOLD_SCH_SALES = 0
		SET @BACKLOG_HOLD_SCH_MARKETING = 0
		SET @BACKLOG_HOLD_SCH_FINANCE = 0
		SET @BACKLOG_HOLD_SCH_OPERATIONS = 0
		SET @BACKLOG_HOLD_SCH = 0

		fetch next from @CoolCursor into @LineItem_ID
	end	

	close @CoolCursor
	deallocate @CoolCursor

	

	/*IF( @QUARTER = 'All' )
	BEGIN
 	SELECT @TOTAL_BACKLOG_OPEN_REQ = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'APPROVED', '0', 'REQUESTED') WHERE ( DATEPART(MONTH,orderDate)-1 >= @START_MONTH AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 <= @END_MONTH)
 	SELECT @TOTAL_BACKLOG_HOLD_REQ = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'REQUESTED') WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 	
 	SELECT @TOTAL_BACKLOG_OPEN_SCH = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'APPROVED', '0', 'SCHEDULE') WHERE ( DATEPART(MONTH,orderDate)-1 >= @START_MONTH AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 <= @END_MONTH)
 	SELECT @TOTAL_BACKLOG_HOLD_SCH = SUM(Amount) FROM fn_backlog_report_for_vertical_report( 0, '0', @quarter, @year, 'HOLD', '0', 'SCHEDULE') WHERE ( DATEPART(MONTH,orderDate)-1 >= @START_MONTH AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 <= @END_MONTH)
 	END
	*/
 	
 	IF( @TOTAL_BACKLOG_OPEN_REQ IS NULL ) SET @TOTAL_BACKLOG_OPEN_REQ = 0
 	IF( @TOTAL_BACKLOG_HOLD_REQ IS NULL ) SET @TOTAL_BACKLOG_HOLD_REQ = 0
 	IF( @TOTAL_BACKLOG_OPEN_SCH IS NULL ) SET @TOTAL_BACKLOG_OPEN_SCH = 0
 	IF( @TOTAL_BACKLOG_HOLD_SCH IS NULL ) SET @TOTAL_BACKLOG_HOLD_SCH = 0
	IF( @TOTAL_BACKLOG_HOLD_REQ_SALES IS NULL ) SET @TOTAL_BACKLOG_HOLD_REQ_SALES = 0	
	IF( @TOTAL_BACKLOG_HOLD_REQ_MARKETING IS NULL ) SET @TOTAL_BACKLOG_HOLD_REQ_MARKETING = 0
	IF( @TOTAL_BACKLOG_HOLD_REQ_FINANCE IS NULL ) SET @TOTAL_BACKLOG_HOLD_REQ_FINANCE = 0
	IF( @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS IS NULL ) SET @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS = 0
	IF( @TOTAL_BACKLOG_HOLD_SCH_SALES IS NULL ) SET @TOTAL_BACKLOG_HOLD_SCH_SALES = 0	
	IF( @TOTAL_BACKLOG_HOLD_SCH_MARKETING IS NULL ) SET @TOTAL_BACKLOG_HOLD_SCH_MARKETING = 0
	IF( @TOTAL_BACKLOG_HOLD_SCH_FINANCE IS NULL ) SET @TOTAL_BACKLOG_HOLD_SCH_FINANCE = 0
	IF( @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS IS NULL ) SET @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS = 0
 

 
 	SELECT @TOTAL_BACKLOG_REQ = @TOTAL_BACKLOG_OPEN_REQ --+ @TOTAL_BACKLOG_HOLD_REQ
 	SELECT @TOTAL_BACKLOG_SCH = @TOTAL_BACKLOG_OPEN_SCH --+ @TOTAL_BACKLOG_HOLD_SCH
 
 	/* CSLIT SHIP DATE AND CSLIT STATUS REPLACED WITH CST SHIP DATE AND CST STATUS -- modified by Gandhi on 1st Oct 2004*/
 	SELECT @TOTAL_SHIPPED_SCH = SUM(CSLIT.CSLIT_QTY_SHIPPED* COOL.COOL_UNIT_COST)
 		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 		WHERE 
 		( ( ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) = @YEAR AND ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 < @TO_MONTH ) ) 
 		AND CST.CST_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 		and SO.SO_Is_Sample = 1  -- Is sample condition added by gandhi on 29th Oct 2004
 	/* Modification ends here - Gandhi */
 	
 	IF( @TOTAL_SHIPPED_SCH IS NULL ) SET @TOTAL_SHIPPED_SCH = 0
 	
 	SET @TOTAL_SHIPPED_REQ = @TOTAL_SHIPPED_SCH
 
 	/*SELECT @TOTAL_RMA_REQ = SUM(QUANTITY * ISNULL(UNIT_PRICE, 0))
 		FROM RMA_TABLE RT, RMA_LINE_ITEM_TABLE RLIT, CUSTOMER CUST, PRODUCT PRO, PROCESS_STEP PS
 		WHERE 	
 		( ( ( DATEPART( MONTH, RT.RMA_REQUESTED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  RT.RMA_REQUESTED_DATE ) = @YEAR AND ( DATEPART( MONTH, RT.RMA_REQUESTED_DATE ))-1 < @TO_MONTH ) ) 
 		AND RT.RMA_ID = RLIT.RMA_ID 
 		AND RMA_STATUS <> 'CANCELLED'
 		AND RT.CUSTOMER_NAME = CUST.C_NAME 
 		AND RLIT.PART_NUMBER = PRO.PART_NUMBER
 		AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
 		AND PS.PS_TYPE_NAME = 'Finished Goods'*/
	--SELECT @TOTAL_RMA_APPROVED_REQ = SUM(Amount)  from fn_rma_for_vertical_report(0, '0', @quarter, @year, 'APPROVED', 'requested')WHERE ( DATEPART(MONTH,orderDate)-1 >= @START_MONTH AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 <= @END_MONTH)
	--SELECT @TOTAL_RMA_SHIPPED_REQ = SUM(Amount)  from fn_rma_for_vertical_report(0, '0', @quarter, @year, 'SHIPPED', 'requested')WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 <= @TO_MONTH)
	SELECT @TOTAL_RMA_REQ = SUM(Amount)  from fn_rma_for_vertical_report(0, '0', @quarter, @year, 'SHIPPED', 'requested', @CURRENT_DATE)WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 
 	IF( @TOTAL_RMA_REQ IS NULL ) SET @TOTAL_RMA_REQ = 0
 
 	SELECT @TOTAL_RMA_SCH = @TOTAL_RMA_REQ
 
 	IF( @TOTAL_RMA_SCH IS NULL ) SET @TOTAL_RMA_SCH = 0
 	
 	/*SELECT @TOTAL_RMA_HOLD_REQ = SUM(QUANTITY * ISNULL(UNIT_PRICE, 0))
 		FROM RMA_TABLE RT, RMA_LINE_ITEM_TABLE RLIT, CUSTOMER CUST, PRODUCT PRO, PROCESS_STEP PS
 		WHERE 	
 		( ( ( DATEPART( MONTH, RT.RMA_REQUESTED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  RT.RMA_REQUESTED_DATE ) = @YEAR AND ( DATEPART( MONTH, RT.RMA_REQUESTED_DATE ))-1 < @TO_MONTH ) ) 
 		AND RT.RMA_ID = RLIT.RMA_ID 
 		AND RMA_STATUS <> 'CANCELLED'
 		AND RMA_STATUS <> 'CLOSE' 
		AND Status IS NULL
		AND RT.CUSTOMER_NAME = CUST.C_NAME 
 		AND RLIT.PART_NUMBER = PRO.PART_NUMBER
 		AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
 		AND PS.PS_TYPE_NAME = 'Finished Goods'*/
	SELECT @TOTAL_RMA_HOLD_REQ = SUM(Amount)  from fn_rma_for_vertical_report(0, '0', @quarter, @year, 'APPROVED', 'requested', @CURRENT_DATE)WHERE ( DATEPART(MONTH,orderDate)-1 >= @MONTH_FROM AND DATEPART(YEAR,orderDate) = @YEAR AND DATEPART(MONTH,orderDate)-1 < @TO_MONTH)
 
 	IF( @TOTAL_RMA_HOLD_REQ IS NULL ) SET @TOTAL_RMA_HOLD_REQ = 0
 
 	SELECT @TOTAL_RMA_HOLD_SCH = @TOTAL_RMA_HOLD_REQ
 
 	/*SELECT @TOTAL_NET_QTD = SUM(COOL_AMOUNT) FROM CUSTOMER_OPEN_ORDER_LINEITEM
 		WHERE ( ( ( DATEPART( MONTH, COOL_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL_BOOKING_DATE ) = @YEAR AND ( DATEPART( MONTH, COOL_BOOKING_DATE ))-1 < @TO_MONTH ) ) */

	select @TOTAL_NET_QTD = sum(cool.cool_amount) from sales_order s, customer_open_order_lineitem cool where ((datepart (month,so_booking_date)-1) >= @MONTH_FROM and (datepart (month,so_booking_date)-1) <= @END_MONTH and datepart (year,so_booking_date) = @year)
	and s.salesOrder_id = cool.salesorder_id and S.SO_Is_Sample = 1
 
 	IF( @TOTAL_NET_QTD IS NULL ) SET @TOTAL_NET_QTD = 0
 	/* on 11/22/2004 Saritha added condition based on timestamp to match with cancellation report */
	SELECT @TOTAL_CANCELLED_QTD = SUM(CCOL_AMOUNT) FROM CUSTOMER_CANCEL_ORDER_LINEITEM 
	WHERE ( ( ( DATEPART( MONTH, TIMESTAMP ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  TIMESTAMP ) = @YEAR AND ( DATEPART( MONTH, TIMESTAMP ))-1 < @TO_MONTH ) ) 
 	
 	IF( @TOTAL_CANCELLED_QTD IS NULL ) SET @TOTAL_CANCELLED_QTD = 0
 
 	SELECT @TOTAL_GROSS_QTD = @TOTAL_NET_QTD + @TOTAL_CANCELLED_QTD
 
 	IF( @TOTAL_GROSS_QTD IS NULL ) SET @TOTAL_GROSS_QTD = 0

 	/* Saritha 11/19/2004 -- Added condition by considering salesOrder table and SO booking date*/
 	SELECT @TOTAL_TURNS_BOOKING = SUM(COOL_AMOUNT) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
 		WHERE ( ( ( DATEPART( MONTH, SO_BOOKING_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  SO_BOOKING_DATE ) = @YEAR AND ( DATEPART( MONTH, SO_BOOKING_DATE ))-1 < @TO_MONTH ) ) 
 		AND ( ( ( DATEPART( MONTH, COOL_REQUESTED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  COOL_REQUESTED_DATE ) = @YEAR AND ( DATEPART( MONTH, COOL_REQUESTED_DATE ))-1 < @TO_MONTH ) ) 
		AND SO.SalesOrder_ID = COOL.SalesOrder_ID and SO.SO_Is_Sample = 1 		

 	IF( @TOTAL_TURNS_BOOKING IS NULL ) SET @TOTAL_TURNS_BOOKING = 0
 
 	IF( @TOTAL_SHIPPED_SCH <> 0)		
 	BEGIN
 		SELECT @BOOK_TO_BILL_QTD = @TOTAL_NET_QTD / @TOTAL_SHIPPED_SCH
 	END
 	ELSE
 	BEGIN
 		SELECT @BOOK_TO_BILL_QTD = -1
 	END
 
 	IF( @BOOK_TO_BILL_QTD IS NULL ) SET @BOOK_TO_BILL_QTD = -1

	--Total Pull-In Quantity
	IF(@QUARTER = 'All')
	BEGIN

		WHILE(@QCOUNT < 4)
		BEGIN
			IF @QCOUNT = 0
			BEGIN
				SET @QUARTER_END_DATE = '03/31/'+CONVERT(VARCHAR,@YEAR) 
				SET @MONTH_FROM = 0
				SET @TO_MONTH = 3
			END
			ELSE IF @QCOUNT = 1
			BEGIN
				SET @QUARTER_END_DATE = '06/30/'+CONVERT(VARCHAR,@YEAR) 
				SET @MONTH_FROM = 3
				SET @TO_MONTH = 6
			END
			ELSE IF @QCOUNT = 2
			BEGIN
				SET @QUARTER_END_DATE = '09/30/'+CONVERT(VARCHAR,@YEAR)		
				SET @MONTH_FROM = 6
				SET @TO_MONTH = 9
			END
			ELSE IF @QCOUNT = 3
			BEGIN
				SET @QUARTER_END_DATE = '12/31/'+CONVERT(VARCHAR,@YEAR)	
				SET @MONTH_FROM = 9
				SET @TO_MONTH = 12
			END

			/*SELECT @PULL_IN_QTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED* COOL.COOL_UNIT_COST),0)
	 		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
	 		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
	 		WHERE 
	 		( ( ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) = @YEAR AND ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 < @TO_MONTH ) ) 
	 		AND CST.CST_STATUS = 'SHIPPED'
	 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
	 		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
	 		AND COOL.PRODUCT_ID = P.PRODUCT_ID
	 		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
	 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
	 		AND SO.SO_Is_Sample = 1 
			AND COOL.COOL_REQUESTED_DATE > DATEADD(DD,7,@QUARTER_END_DATE)*/
			SELECT @PULL_IN_QTY = ISNULL(SUM( COOL_AMOUNT ),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE COOL_REQUESTED_DATE > @QUARTER_END_DATE AND COOL_REQUESTED_DATE < DATEADD(DD,7,@QUARTER_END_DATE)
			AND ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE) <= @QUARTER_END_DATE

			SET @TOTAL_PULL_IN_QTY = @TOTAL_PULL_IN_QTY + @PULL_IN_QTY
			SET @QCOUNT = @QCOUNT + 1
		END
	END
	ELSE
	BEGIN
		/*SELECT @TOTAL_PULL_IN_QTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED* COOL.COOL_UNIT_COST),0)
 		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 		WHERE 
 		( ( ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) = @YEAR AND ( DATEPART( MONTH, CST.CST_SHIP_DATE ))-1 < @TO_MONTH ) ) 
 		AND CST.CST_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 		AND SO.SO_Is_Sample = 1 
		AND COOL.COOL_REQUESTED_DATE > DATEADD(DD,7,@QUARTER_END_DATE)*/

		SELECT @TOTAL_PULL_IN_QTY = ISNULL(SUM( COOL_AMOUNT ),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE COOL_REQUESTED_DATE > @QUARTER_END_DATE AND COOL_REQUESTED_DATE < DATEADD(DD,7,@QUARTER_END_DATE)
		AND ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE) <= @QUARTER_END_DATE

	END
 	
 	INSERT INTO @RESULTS VALUES(@TOTAL_SHIPPED_REQ, @TOTAL_SHIPPED_SCH, @TOTAL_BACKLOG_REQ, @TOTAL_BACKLOG_SCH, @TOTAL_RMA_REQ, @TOTAL_RMA_SCH, @TOTAL_BACKLOG_HOLD_REQ, @TOTAL_BACKLOG_HOLD_SCH, @TOTAL_RMA_HOLD_REQ, @TOTAL_RMA_HOLD_SCH, @TOTAL_GROSS_QTD, @TOTAL_CANCELLED_QTD, @TOTAL_NET_QTD, @TOTAL_TURNS_BOOKING, @BOOK_TO_BILL_QTD, @TOTAL_BACKLOG_HOLD_REQ_SALES, @TOTAL_BACKLOG_HOLD_REQ_MARKETING, @TOTAL_BACKLOG_HOLD_REQ_FINANCE, @TOTAL_BACKLOG_HOLD_REQ_OPERATIONS, @TOTAL_BACKLOG_HOLD_SCH_SALES, @TOTAL_BACKLOG_HOLD_SCH_MARKETING, @TOTAL_BACKLOG_HOLD_SCH_FINANCE, @TOTAL_BACKLOG_HOLD_SCH_OPERATIONS, @TOTAL_PULL_IN_QTY )
 
 	SELECT * FROM @RESULTS
 END
